/*
    Description: Updates the FieldDesign table with default VALUES FROM the Imis.UD_Field
    The table needs to be in sync so you dont have to query both datasets.
*/
CREATE PROCEDURE [dbo].[BAESyncImisUserDefinedTables] AS
INSERT INTO FieldDesign
SELECT UD_Field.TABLE_NAME, UD_Field.FIELD_NAME, 0, '', '', '' FROM UD_Field
WHERE FIELD_NAME COLLATE database_default NOT IN (SELECT DISTINCT FieldDesign.FieldName FROM FieldDesign WHERE TableName COLLATE database_default = TABLE_NAME COLLATE database_default)

DELETE FROM FieldDesign WHERE FieldDesign.FieldName COLLATE database_default
    NOT IN (SELECT udf.FIELD_NAME FROM UD_Field udf WHERE FieldDesign.TableName COLLATE database_default = udf.TABLE_NAME COLLATE database_default);

DELETE FROM FieldDesignSectionLookup WHERE FieldDesignSectionLookup.FieldName
    NOT IN (SELECT fd.FieldName FROM FieldDesign fd WHERE FieldDesignSectionLookup.TableName COLLATE database_default = fd.TableName COLLATE database_default);
/*
DELETE FROM FieldSection WHERE FieldSection.FieldSectionID
    NOT IN (SELECT fdsl.FieldSectionID FROM FieldDesignSectionLookup fdsl)
*/


-----------------------------------
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF

GO